<?php

namespace App\Http\Controllers\admin;

use Illuminate\Http\Request;
use DB;
use App\Http\Requests;
use App\Http\Controllers\Controller;

class NewsController extends Controller
{
    public function getIndex(Request $request)
    {
    	//检测是否有条件查询
      	if ($request->input('keywords')) {
          // $news = DB::select("select *,count(cid) as num from news left join users on news.nuid=users.uid left join comments on news.nid = comments.cnid  where nname like '%".$request->input('keywords')."%' or username like '%".$request->input('keywords')."%' or ntitle like '%".$request->input('keywords')."%' group by nid order by nid desc ");
            $news = DB::table('news')->select(DB::raw('*,count(comments.cid) as num'))->leftJoin('users', 'news.nuid', '=', 'users.uid')->leftJoin('comments', 'news.nid', '=', 'comments.cnid')->where('news.nname','like','%'.$request->input('keywords').'%')->orWhere('news.ntitle','like','%'.$request->input('keywords').'%')->orWhere('users.username','like','%'.$request->input('keywords').'%')->groupBy('news.nid')->orderBy('news.nid','desc')->paginate(5);
        }else{
            $news = DB::table('news')->select(DB::raw('*,count(comments.cid) as num'))->leftJoin('users', 'news.nuid', '=', 'users.uid')->leftJoin('comments', 'news.nid', '=', 'comments.cnid')->groupBy('news.nid')->orderBy('news.nid','desc')->paginate(5);
            // $news = DB::select("select *,count(cid) as num from news left join users on news.nuid=users.uid left join comments on news.nid = comments.cnid group by nid order by nid desc");
        }


        //川哥的sql
		// select distinct news.*,users.username from news left join comments on news.nid = comments.cnid left join users on news.nuid = users.uid
        // select *,count(cid) as num from news left join users on news.nuid=users.uid left join comments on news.nid = comments.cnid group by cnid order by nid desc
        
        $list = $request->all();
        //解析模版,,分配变量
    	return view('admin.news.index',['news'=>$news,'list'=>$list]);

    	
    }

    //查询评论列表
    public function getSelect(Request $request)
    {
    	// dd($request->input('nid'));
    	//查询数据
        $comment = DB::select("select *  from comments left join users on comments.cuid=users.uid where cnid = ".$request->input('nid'));
        //以json格式回传数据
        echo json_encode($comment);
    }

    
    //添加新闻
    public function getAdd()
    {
    	//查询所有新闻管理员
    	$user = DB::select('select * from users where blockauth = 2');
    	return view('admin.news.add',['user'=>$user]);
    }

    //新闻添加执行方法
    public function postDoadd(Request $request)
    {
        //提取参数
    	$data = $request->except('_token');
        //压入时间
    	$data['nctime'] = date('Y-m-d H:i:s',time());
        //执行数据库插入
    	$res = DB::table('news')->insert($data);
        //判断结果
    	if ($res) {
            //执行成功
            return redirect('/admin/news/index')->with('success','添加成功');
        }else{
            //执行失败
            return back()->with('error','添加失败'); 
        }
    }

    //删除
    public function getDelete(Request $request)
    {
    	// dd($request->all());
        //删除新闻
    	$resn = DB::table('news')->where('nid',$request->input('nid'))->delete();
    	//删除评论
        $resc = DB::table('comments')->where('cnid',$request->input('nid'))->delete();
    	// dd($resn);
        if ($resn || $resc) {
           //删除成功
          echo 1;
         }else{
          //删除失败
          echo 0;
         }
    }

    //修改
    public function getEdit($id)
    {
    	//获取信息
    	$new = DB::table('news')->where('nid',$id)->first();
    	//解析模版 分配变量
    	return view('admin.news.edit',['new'=>$new]);


    }

    //执行修改
    public function postDoedit(Request $request)
    {
    	// dd($request->all());
    	$data = $request->except('_token');
    	// dd($data);
    	$res = DB::table('news')->where('nid',$data['nid'])->update($data);
    	if ($res) {
    		//添加成功
    		return redirect('/admin/news/index')->with('success','修改成功');
    	}else{
    		//添加失败
    		return back()->with('error','修改失败'); 
    	}
    }

}
